﻿using Microsoft.Data.SqlClient;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace SqlHelp
{
    /// <summary>
    /// Sql Helper
    /// </summary>
    public class SqlHelper : IDisposable
    {
        private IDbConnection _conn;
        private IDbCommand _command;
        private DataType _dbType;
        private string _ConnectionStrings;//连接字符串

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return _ConnectionStrings; }
            set { _ConnectionStrings = value; }
        }

        /// <summary>
        /// 数据库类型
        /// </summary>
        public DataType DbType
        {
            get { return _dbType; }
            set { _dbType = value; }
        }

        public SqlHelper()
        {

        }

        public SqlHelper(string ConnectionStrings)
        {
            _ConnectionStrings = ConnectionStrings;
            _dbType = DataType.SqlServer;
            CreateConnection();
        }

        public SqlHelper(string ConnectionStrings, DataType dbType)
        {
            _ConnectionStrings = ConnectionStrings;
            _dbType = dbType;
            CreateConnection();
        }

        /// <summary>
        /// 设置数据库连接
        /// </summary>
        /// <param name="ConnectionStrings"></param>
        public void SetSqlConnection(string ConnectionStrings)
        {
            _ConnectionStrings = ConnectionStrings;
            _dbType = DataType.SqlServer;
            CreateConnection();
        }

        /// <summary>
        /// 设置数据库连接
        /// </summary>
        /// <param name="ConnectionStrings"></param>
        /// <param name="dbType"></param>
        public void SetSqlConnection(string ConnectionStrings, DataType dbType)
        {
            _ConnectionStrings = ConnectionStrings;
            _dbType = dbType;
            CreateConnection();
        }

        /// <summary>
        /// 执行Sql,返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="Parameter"></param>
        /// <returns></returns>
        public DataTable ExecuteSqlTable(string sql, object Parameter = null)
        {
            //如果连接有问题，则重新连接
            if (_conn == null || _conn.State == ConnectionState.Closed || _conn.State == ConnectionState.Broken)
            {
                CreateConnection();
            }
            _command = CreateDbCommand();
            _command.Connection = _conn;
            _command.CommandText = sql;
            var Parameters = ChangeSqlParameter(Parameter);
            if (Parameters != null && Parameters.Count() > 0)
            {
                foreach (var P in Parameters)
                {
                    _command.Parameters.Add(P);
                }
            }
            return QueryDataTable(_command);
        }

        /// <summary>
        /// 执行Sql，返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="Parameter"></param>
        /// <returns></returns>
        public int ExecuteSqlNonQuery(string sql, object Parameter = null)
        {
            //如果连接有问题，则重新连接
            if (_conn == null || _conn.State == ConnectionState.Closed || _conn.State == ConnectionState.Broken)
            {
                CreateConnection();
            }
            _command = CreateDbCommand();
            _command.Connection = _conn;
            _command.CommandText = sql;
            var Parameters = ChangeSqlParameter(Parameter);
            if (Parameters != null && Parameters.Count() > 0)
            {
                foreach (var P in Parameters)
                {
                    _command.Parameters.Add(P);
                }
            }
            return _command.ExecuteNonQuery();
        }

        /// <summary>
        /// 连接数据库
        /// </summary>
        private void CreateConnection()
        {
            if (_conn != null)
            {
                Dispose();
            }
            _conn = CreateDbConnection();
            _conn.Open();
        }

        /// <summary>
        /// 查询DataTable
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        private DataTable QueryDataTable(IDbCommand command)
        {
            IDbDataAdapter dataAdapter = CreateDbDataAdapter();
            dataAdapter.SelectCommand = command;
            DataSet resultSet = new DataSet();
            dataAdapter.Fill(resultSet);
            return resultSet.Tables[0];
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            _conn.Close();
            _conn.Dispose();
            _conn = null;
        }

        /// <summary>
        /// 对象转换为DbParameter(反射)
        /// </summary>
        /// <param name="Parameter"></param>
        /// <returns></returns>
        private IEnumerable<IDataParameter> ChangeSqlParameter(object Parameter)
        {
            if (Parameter == null)
            {
                return null;
            }
            List<IDataParameter> DbParameters = new List<IDataParameter>();
            FieldInfo[] objFields = Parameter.GetType().GetFields();
            foreach (FieldInfo fieldInfo in objFields)
            {
                var para = CreateDbParameter(fieldInfo.Name, fieldInfo.GetValue(Parameter));
                DbParameters.Add(para);
            }
            PropertyInfo[] propertyInfos = Parameter.GetType().GetProperties();
            foreach (PropertyInfo prop in propertyInfos)
            {
                var parameterName = prop.Name;
                var value = prop.GetValue(Parameter);
                var para = CreateDbParameter(prop.Name, prop.GetValue(Parameter));
                DbParameters.Add(para);
            }
            return DbParameters;
        }

        /// <summary>
        /// 创建DbParameter
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ParameterName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private IDataParameter CreateDbParameter(string ParameterName, object value)
        {
            switch (_dbType)
            {
                case DataType.MySql:
                    return new MySqlParameter(ParameterName, value);
                case DataType.SqlServer:
                    return new SqlParameter(ParameterName, value);
                default:
                    throw new Exception("暂不支持此数据库");
            }
        }

        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns></returns>
        private IDbConnection CreateDbConnection()
        {
            if (string.IsNullOrWhiteSpace(_ConnectionStrings))
            {
                throw new Exception("请设置数据库连接字符串");
            }
            switch (_dbType)
            {
                case DataType.MySql:
                    return new MySqlConnection(_ConnectionStrings);
                case DataType.SqlServer:
                    return new SqlConnection(_ConnectionStrings);
                default:
                    throw new Exception("暂不支持此数据库");
            }
        }

        /// <summary>
        /// 创建数据库执行类
        /// </summary>
        /// <returns></returns>
        private IDbCommand CreateDbCommand()
        {
            switch (_dbType)
            {
                case DataType.MySql:
                    return new MySqlCommand();
                case DataType.SqlServer:
                    return new SqlCommand();
                default:
                    throw new Exception("暂不支持此数据库");
            }
        }

        /// <summary>
        /// 创建数据库阅读器
        /// </summary>
        /// <returns></returns>
        private IDbDataAdapter CreateDbDataAdapter()
        {
            switch (_dbType)
            {
                case DataType.MySql:
                    return new MySqlDataAdapter();
                case DataType.SqlServer:
                    return new SqlDataAdapter();
                default:
                    throw new Exception("暂不支持此数据库");
            }
        }

    }

    public enum DataType
    {
        SqlServer,
        MySql
    }

}
